const xlsx = require('xlsx');
const { pool } = require('../config/db');

/**
 * Excel导入服务
 */
class ExcelService {
  /**
   * 解析Excel文件
   * @param {string} filePath - Excel文件路径
   * @returns {Array} - 解析后的数据数组
   */
  parseExcel(filePath) {
    try {
      // 读取Excel文件
      const workbook = xlsx.readFile(filePath);
      
      // 获取第一个工作表
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      
      // 将工作表转换为JSON数组
      const data = xlsx.utils.sheet_to_json(worksheet);
      
      return data;
    } catch (error) {
      console.error('解析Excel文件失败:', error.message);
      throw new Error('Excel文件解析失败');
    }
  }
  
  /**
   * 获取字段值（支持多种列名）
   * @param {Object} row - Excel行数据
   * @param {Array} fieldNames - 可能的字段名列表
   * @returns {*} - 字段值
   */
  getFieldValue(row, fieldNames) {
    for (const fieldName of fieldNames) {
      if (row[fieldName] !== undefined && row[fieldName] !== null && row[fieldName] !== '') {
        return row[fieldName];
      }
    }
    return null;
  }

  /**
   * 验证Excel数据
   * @param {Array} data - Excel数据
   * @returns {Object} - 验证结果
   */
  validateData(data) {
    const errors = [];
    const validData = [];
    
    // 字段映射配置（支持多种列名）
    const fieldMapping = {
      name: ['姓名', '名字', '人员姓名'],
      avatar_url: ['头像', '头像链接', '头像URL', '照片'],
      position: ['岗位', '职务', '职位', '当前职务', '现任职务'],
      rank: ['军衔', '军阶', '级别'],
      biography: ['简介', '个人简介', '人物简介', '履历'],
      data_source: ['数据源', '来源', '数据来源', '信息来源']
    };
    
    data.forEach((row, index) => {
      const rowErrors = [];
      
      // 获取姓名（必填）
      const name = this.getFieldValue(row, fieldMapping.name);
      if (!name || name.toString().trim() === '') {
        rowErrors.push('姓名不能为空');
      }
      
      if (rowErrors.length > 0) {
        errors.push({
          row: index + 2, // Excel行号从2开始（1是表头）
          errors: rowErrors
        });
      } else {
        validData.push({
          name: name,
          avatar_url: this.getFieldValue(row, fieldMapping.avatar_url),
          position: this.getFieldValue(row, fieldMapping.position),
          rank: this.getFieldValue(row, fieldMapping.rank),
          biography: this.getFieldValue(row, fieldMapping.biography),
          data_source: this.getFieldValue(row, fieldMapping.data_source)
        });
      }
    });
    
    return {
      success: errors.length === 0,
      validData,
      errors
    };
  }
  
  /**
   * 批量导入数据到数据库
   * @param {Array} data - 待导入的数据
   * @returns {Promise<Object>} - 导入结果
   */
  async importToDatabase(data) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      // 检查数据源是否存在的SQL
      const checkSql = `
        SELECT id FROM raw_personnel 
        WHERE data_source = ? 
        LIMIT 1
      `;
      
      // 插入数据的SQL
      const insertSql = `
        INSERT INTO raw_personnel 
        (name, avatar_url, position, \`rank\`, biography, data_source, process_status)
        VALUES (?, ?, ?, ?, ?, ?, 'pending')
      `;
      
      let successCount = 0;
      let failCount = 0;
      let skipCount = 0;
      const errors = [];
      const skipped = [];
      
      for (let i = 0; i < data.length; i++) {
        try {
          const row = data[i];
          
          // 检查数据源是否已存在（如果有提供数据源）
          if (row.data_source && row.data_source.toString().trim() !== '') {
            const [existingRows] = await connection.execute(checkSql, [row.data_source]);
            
            if (existingRows.length > 0) {
              // 数据源已存在，跳过该记录
              skipCount++;
              skipped.push({
                row: i + 1,
                name: row.name,
                data_source: row.data_source,
                reason: '数据源已存在'
              });
              continue;
            }
          }
          
          // 插入数据
          await connection.execute(insertSql, [
            row.name,
            row.avatar_url,
            row.position,
            row.rank,
            row.biography,
            row.data_source
          ]);
          successCount++;
        } catch (error) {
          failCount++;
          errors.push({
            row: i + 1,
            data: data[i],
            error: error.message
          });
        }
      }
      
      await connection.commit();
      
      return {
        success: true,
        successCount,
        failCount,
        skipCount,
        errors,
        skipped
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  
  /**
   * 完整的导入流程
   * @param {string} filePath - Excel文件路径
   * @returns {Promise<Object>} - 导入结果
   */
  async importExcel(filePath) {
    try {
      // 1. 解析Excel
      const rawData = this.parseExcel(filePath);
      
      if (rawData.length === 0) {
        return {
          success: false,
          message: 'Excel文件中没有数据'
        };
      }
      
      // 2. 验证数据
      const validation = this.validateData(rawData);
      
      if (!validation.success) {
        return {
          success: false,
          message: '数据验证失败',
          errors: validation.errors
        };
      }
      
      // 3. 导入数据库
      const result = await this.importToDatabase(validation.validData);
      
      return {
        success: true,
        message: '导入成功',
        totalRows: rawData.length,
        ...result
      };
    } catch (error) {
      console.error('Excel导入失败:', error.message);
      return {
        success: false,
        message: error.message
      };
    }
  }
}

module.exports = new ExcelService();

